from app import app
from flask import request, jsonify
from app.models import *
from app.admin.admin_login import admin_require
from flask_restful import inputs
from sqlalchemy import or_
from app.setting import *
from app.admin.ulits.chats import Chats
from app.libs.QINIU import delect as QiniuDel

# 获取更多备注
@app.route('/pdd/admin/home/comment', methods=["get"])
@admin_require
def admin_home_comment(aid):
    id = request.args.get('aid', type=int)
    page = request.args.get('page', default=1, type=int)
    pageSize = request.args.get('pageSize', default=10, type=int)
    sql = list()

    if id:
        sql.append(UserComment.aid == id)
    else:
        sql.append(UserComment.aid == aid.id)

    comment = UserComment.query.filter(*sql).paginate(page, per_page=pageSize, error_out=False)
    data = [{
        "phone": i.user.phone if aid.job.allot else i.user.phone.replace(i.user.phone[3:7], '****'),
        "photo": i.admin.userPhoto,
        "name":i.admin.name,
        "content": i.content,
        "createtime": str(i.createtime)
    } for i in comment.items]

    return jsonify(code=0, msg="成功",
                   page=comment.page,
                   total=comment.total,
                   pages=comment.pages,
                   rows=data, )


# 个人中心
@app.route('/pdd/admin/home', methods=["get"])
@admin_require
def admin_home(aid):
    id = request.args.get('aid', type=int)

    if id is None:
        id = aid.id

    admin = Admin.query.get(id)
    if not admin:
        return jsonify(code=-104, msg="未找到该用户,页面丢失")

    admin = {
        "aid":admin.id,
        "jobName": admin.job.jobName if admin.jid is not None else "暂无职位",
        "userPhoto": admin.userPhoto,
        "phone": admin.phone if admin.phone else "暂无信息",
        "wechat": admin.wechat if admin.wechat else "暂无信息",
        "birthdate": str(admin.birthdate) if admin.birthdate else "暂无信息",
        "address": admin.address if admin.address else "暂无信息",
        "sex": "小哥哥" if admin.sex else "小姐姐",
        "intro":admin.intro,
    }
    # 资源总数
    callSum = db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(User.aid == id).first().sum
    # 剩余资源数
    callSurplus = db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
        User.aid == id,
        UserSell.call_status == 0,
    ).first().sum
    # 已加微信数
    wechatSum = db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
        User.aid == id,
        UserSell.wechat_status == 2,
    ).first().sum
    # 有意向数
    intentSum = db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
        User.aid == id,
        UserSell.Intent_status == 1,
    ).first().sum

    coopSum = db.session.query(db.func.count(UserCoop.id).label("sum")).filter(
        UserCoop.aid == id,
        UserCoop.endTime >= datetime.datetime.now().date()

    ).first().sum

    NOW = datetime.datetime.now()

    '''
     以下获取昨日数据
    '''
    yd_call = db.session.query(db.func.count(SellDays.id).label("sum")).filter(SellDays.aid == id, or_(SellDays.call_status != None,SellDays.call_status !=0),
                                                                               db.cast(SellDays.createtime,
                                                                                       db.DATE) == NOW.date() - datetime.timedelta(
                                                                                   days=1)).first().sum
    yd_call_2_3 = db.session.query(db.func.count(SellDays.id).label("sum")).filter(SellDays.aid == id,
                                                                                   or_(SellDays.call_status == 2,
                                                                                       SellDays.call_status == 3),
                                                                                   db.cast(SellDays.createtime,
                                                                                           db.DATE) == NOW.date() - datetime.timedelta(
                                                                                       days=1)).first().sum
    yd_wechat_2 = db.session.query(db.func.count(SellDays.id).label("sum")).filter(SellDays.aid == id,
                                                                                   SellDays.wechat_status == 2,
                                                                                   db.cast(SellDays.createtime,
                                                                                           db.DATE) == NOW.date() - datetime.timedelta(
                                                                                       days=1)).first().sum
    yd_wechat_1 = db.session.query(db.func.count(SellDays.id).label("sum")).filter(SellDays.aid == id,
                                                                                   SellDays.wechat_status == 1,
                                                                                   db.cast(SellDays.createtime,
                                                                                           db.DATE) == NOW.date() - datetime.timedelta(
                                                                                       days=1)).first().sum
    yd_intent_1 = db.session.query(db.func.count(SellDays.id).label("sum")).filter(SellDays.aid == id,
                                                                                   SellDays.Intent_status == 1,
                                                                                   db.cast(SellDays.createtime,
                                                                                           db.DATE) == NOW.date() - datetime.timedelta(
                                                                                       days=1)).first().sum
    yd_intent_2 = db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(User.aid == id,
                                                                                              UserSell.Intent_status == 2,
                                                                                              db.cast(
                                                                                                  SellDays.createtime,
                                                                                                  db.DATE) == NOW.date() - datetime.timedelta(
                                                                                                  days=1)).first().sum
    yd_intent_3 = db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(User.aid == id,
                                                                                              UserSell.Intent_status == 3,
                                                                                              db.cast(
                                                                                                  SellDays.createtime,
                                                                                                  db.DATE) == NOW.date() - datetime.timedelta(
                                                                                                  days=1)).first().sum
    yd_coopSum = db.session.query(db.func.count(UserCoop.id).label("sum")).filter(UserCoop.aid == id,
                                                                                  db.cast(UserCoop.createtime,
                                                                                          db.DATE) == NOW.date() - datetime.timedelta(
                                                                                      days=1)).first().sum

    userComment = UserComment.query.filter(UserComment.aid == id).order_by(UserComment.createtime.desc()).limit(4)

    # 销售数据
    sellData = {
        "callSum": callSum,
        "callSurplus": callSurplus,
        "wechatSum": wechatSum,
        "intentSum": intentSum,
        "data":[
            {
                "name":"电话量",
                "data":{
                    "sum":db.session.query(db.func.count(SellDays.id).label("sum")).filter(
                                SellDays.aid == id,
                                or_(SellDays.call_status != None,SellDays.call_status !=0),
                            ).first().sum,
                    "yd_sum": yd_call,
                    "up": True if yd_call > 0 else False
                }
            },
            {
                "name": "加微信量",
                "data": {
                    "sum": wechatSum,
                    "yd_sum": yd_wechat_2,
                    "up": True if yd_wechat_2 > 0 else False
                }
            },
            {
                "name": "意向客户",
                "data": {
                    "sum": intentSum,
                    "yd_sum": yd_intent_1,
                    "up": True if yd_intent_1 > 0 else False
                }
            },
            {
                "name": "合作中",
                "data": {
                    "sum": coopSum,
                    "yd_sum": yd_coopSum,
                    "up": True if yd_coopSum > 0 else False
                }
            },
            {
                "name": "拒接/挂断/停机",
                "data": {
                    "sum": db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
                                User.aid == id,
                                or_(UserSell.call_status == 2, UserSell.call_status == 3),
                            ).first().sum,
                    "yd_sum": yd_call_2_3,
                    "up": True if yd_call_2_3 > 0 else False
                }
            },
            {
                "name": "待加微信",
                "data": {
                    "sum": db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
                            User.aid == id,
                            UserSell.wechat_status == 1,
                        ).first().sum,
                    "yd_sum": yd_wechat_1,
                    "up": True if yd_wechat_1 > 0 else False
                }
            },
            {
                "name": "奇葩客户",
                "data": {
                    "sum": db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
                            User.aid == id,
                            UserSell.Intent_status == 2,
                            ).first().sum,
                    "yd_sum": yd_intent_2,
                    "up": True if yd_intent_2 > 0 else False
                }
            },
            {
                "name": "非卖家",
                "data": {
                    "sum": db.session.query(db.func.count(User.id).label("sum")).join(UserSell).filter(
                        User.aid == id,
                        UserSell.Intent_status == 3,
                    ).first().sum,
                    "yd_sum": yd_intent_3,
                    "up": True if yd_intent_3 > 0 else False
                }
            }
        ],
        "comment":[{
            "phone": i.user.phone if aid.job.allot  else i.user.phone.replace(i.user.phone[3:7], '****'),
            "name": i.admin.name,
            "userPhoto": i.user.userPhoto,
            "content": i.content,
            "createtime": i.createtime.strftime('%m-%d %H:%M')
        } for i in userComment]
    }

    return jsonify(code=0, msg="获取成功", data={
        "admin": admin,
        "sellData": sellData,
    })




# 个人中心
@app.route('/pdd/admin/update/info', methods=["post"])
@admin_require
def admin_update_info(aid):
    nick = request.form.get("nick",type=str)
    name = request.form.get("name", type=str)
    sex = request.form.get("sex", type=inputs.boolean)
    address = request.form.get("address", type=str)
    phone = request.form.get("phone", type=str)
    birthdate = request.form.get("birthdate", type=str)
    intro = request.form.get("intro", type=str)
    wechat = request.form.get("wechat",type=str)
    if nick:
        aid.nick = nick

    if wechat:
        aid.wechat = wechat

    if name:
        aid.name = name

    if sex is not None:
        aid.sex = sex

    if address:
        aid.address = address

    if phone:
        aid.phone = phone

    if birthdate:
        aid.birthdate = birthdate

    if intro:
        aid.intro = intro

    try:
        db.session.commit()
        return jsonify(code=0,msg="修改成功")
    except:
        db.session.rollback()
        return jsonify(code=0, msg="修改失败")



# 获取趋势图
@app.route('/pdd/admin/home/charts', methods=["post"])
@admin_require
def admin_home_charts(aid):
    id = request.args.get('aid', type=int)

    if id is None:
        id = aid.id

    data_type = request.form.get("data_type",default=1,type=int)

    if data_type not in [1,2,3,4,5]:
        return jsonify(code=-1,msg="请求出错～")

    chats = Chats()
    data = chats.home_chats(type=data_type,aid=id,days=7)
    return jsonify(code=0,msg="获取成功",data=data)


# SEO数据分析
@app.route('/pdd/admin/seo/data', methods=["get"])
@admin_require
def admin_seo(aid):

    if not aid.job.Seo:
        return jsonify(code=-103,msg="无权访问,需要管理员授权")
    chats = Chats()
    vdata = chats.seo_header_visitor()
    rdata = chats.seo_header_register()
    data = [
      {"name":"访客数","count":vdata.get("sum").get("count"),"y_count":vdata.get("sum").get("yeday")},
      {"name": "百度访客", "count": vdata.get("baidu").get("count"), "y_count": vdata.get("baidu").get("yeday")},
      {"name": "搜狗访客", "count": vdata.get("sougou").get("count"), "y_count": vdata.get("sougou").get("yeday")},
      {"name": "360访客", "count": vdata.get("esou").get("count"), "y_count": vdata.get("esou").get("yeday")},
      {"name": "注册数", "count": rdata.get("sum").get("count"), "y_count": rdata.get("sum").get("yeday")},
      {"name": "百度注册", "count": rdata.get("baidu").get("count"), "y_count": rdata.get("baidu").get("yeday")},
      {"name": "搜狗注册", "count": rdata.get("sougou").get("count"), "y_count": rdata.get("sougou").get("yeday")},
      {"name": "360注册", "count": rdata.get("esou").get("count"), "y_count": rdata.get("esou").get("yeday")},
    ]


    return jsonify(code=0,msg="获取成功",data=data)

# 获取趋势图
@app.route('/pdd/admin/seo/charts', methods=["post"])
@admin_require
def admin_seo_charts(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")

    data_type = request.form.get("data_type",default=1,type=int)

    if data_type not in [1,2,3,4]:
        return jsonify(code=-1,msg="请求出错～")

    chats = Chats()
    data = chats.seo_chats(type=data_type,days=15)
    return jsonify(code=0,msg="获取成功",data=data)

# 获取饼图
@app.route('/pdd/admin/seo/pie', methods=["post"])
@admin_require
def admin_seo_pie(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")

    data_type = request.form.get("data_type",default=1,type=int)

    if data_type not in [1,2,3,4]:
        return jsonify(code=-1,msg="请求出错～")

    chats = Chats()


    data = chats.seo_keyword_pie(type=data_type)



    return jsonify(code=0,msg="获取成功",data=data)

# 获取饼图2
@app.route('/pdd/admin/seo/pie/2', methods=["post"])
@admin_require
def admin_seo_pie_2(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")

    chats = Chats()


    data = chats.seo_source_pie()

    return jsonify(code=0,msg="获取成功",data=data)

# 获取城市柱状图
@app.route('/pdd/admin/seo/bar', methods=["post"])
@admin_require
def admin_seo_bar(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")

    chats = Chats()


    data = chats.seo_bar()

    return jsonify(code=0,msg="获取成功",data=data)

# 词汇分析趋势图
@app.route('/pdd/admin/anal/keyword/charts', methods=["post"])
@admin_require
def admin_anal_keyword_charts(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")
    keyword = request.form.get("keyword",type=str)
    data_type = request.form.get("data_type",default=1,type=int)
    if keyword is None:
        return jsonify(code=-104,msg="暂无数据～")
    if data_type not in [1,2,3,4]:
        return jsonify(code=-1,msg="请求出错～")

    chats = Chats()
    data = chats.anal_keyword_chats(keyword=keyword,type=data_type,days=15)
    return jsonify(code=0,msg="获取成功",data=data)



# 词汇分析获取关键词访客列表
@app.route('/pdd/admin/anal/keyword/list', methods=["get"])
@admin_require
def admin_anal_keyword_list(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")
    keyword = request.args.get("keyword",type=str)
    page = request.args.get('page', default=1, type=int)
    pageSize = request.args.get('pageSize', default=10, type=int)
    sql = list()


    if keyword:
        sql.append(Visitor.keyword == keyword)
    else:
        return jsonify(code=0, msg="成功",
                       page=0,
                       total=0,
                       pages=1,
                       rows=[],)

    pager = Visitor.query.filter(*sql).paginate(page, per_page=pageSize, error_out=False)
    data = [{
        "sign": i.sign,
        "addr": i.addr,
        "landing": i.landing,
        "leave": i.leave,
        "source": i.referer,
        "city":i.city,
        "islogin":i.islogin,
        "createtime":str(i.createtime),
    } for i in pager.items]

    return jsonify(code=0, msg="成功",
                   page=pager.page,
                   total=pager.total,
                   pages=pager.pages,
                   rows=data, )


# 进站关键词榜单
@app.route('/pdd/admin/anal/keyword/rank', methods=["get"])
@admin_require
def admin_anal_keyword_rank(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")

    data_type = request.args.get("data_type",default=1,type=int)

    if not data_type in [1,2,3]:
        return jsonify(code=-1, msg="访问出错")

    sql = list()
    Now = datetime.datetime.now()
    sql.append(Visitor.keyword !="其他访客")
    sql.append(Visitor.keyword !="直接进入")
    if data_type ==2:
        sql.append(db.cast(Visitor.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=7))

    if data_type ==3:
        sql.append(db.cast(Visitor.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=30))

    data = []

    ret = db.session.query(Visitor.keyword, db.func.count(Visitor.id).label("count")).filter(*sql).order_by(db.desc("count")).group_by(Visitor.keyword).limit(4).all()

    data.append({
        "name": "关键词访问排行",
        "data": [i.keyword for i in ret]
    })

    ret = db.session.query(Visitor.keyword, db.func.count(Visitor.id).label("count")).filter(*sql).filter(
        Visitor.source == "百度搜索"
    ).order_by(db.desc("count")).group_by(Visitor.keyword).limit(4).all()

    data.append({
        "name": "百度关键词访问排行",
        "data": [i.keyword for i in ret]
    })

    ret = db.session.query(Visitor.keyword, db.func.count(Visitor.id).label("count")).filter(*sql).filter(
        Visitor.source == "360搜索"
    ).order_by(db.desc("count")).group_by(Visitor.keyword).limit(4).all()

    data.append({
        "name": "360关键词访问排行",
        "data": [i.keyword for i in ret]
    })

    ret = db.session.query(Visitor.keyword, db.func.count(Visitor.id).label("count")).filter(*sql).filter(
        Visitor.source == "搜狗搜索"
    ).order_by(db.desc("count")).group_by(Visitor.keyword).limit(4).all()

    data.append({
        "name": "搜狗关键词访问排行",
        "data": [i.keyword for i in ret]
    })
    return jsonify(code=0,msg="获取成功",data=data)




# 词汇分析获取关键词信息
@app.route('/pdd/admin/anal/keyword/data', methods=["get"])
@admin_require
def admin_anal_keyword_data(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")
    page = request.args.get('page', default=1, type=int)
    pageSize = request.args.get('pageSize', default=10, type=int)
    day_type = request.args.get('day_type', default=1, type=int)
    reg_sort = request.args.get('reg_sort', type=str)
    regRate_sort = request.args.get('regRate_sort', type=str)
    vis_sort = request.args.get('vis_sort', type=str)
    hold_sort = request.args.get('hold_sort', type=str)
    sql = list()
    sql.append(Visitor.keyword != "其他访客")
    sql.append(Visitor.keyword != "直接进入")
    Now = datetime.datetime.now()
    if not day_type in [1,2,3,4]:
        return jsonify(code=-1,msg="请求出错～")
    if day_type == 2:
        sql.append(db.cast(Visitor.createtime, db.DATE) == Now.date() - datetime.timedelta(days=1))
    if day_type == 3:
        sql.append(db.cast(Visitor.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=7))
    if day_type == 4:
        sql.append(db.cast(Visitor.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=30))

    # 获取查询总词汇数
    sum = db.session.query(db.func.count(Visitor.id).label("sum")).filter(*sql).first().sum

    # 组装查询语句
    query = [Visitor.keyword, db.func.count(Visitor.id).label("vis"), Visitor.createtime]
    # 查询注册人数
    query.append(db.func.sum(db.func.IF((Visitor.islogin), 1, 0)).label("reg"))
    # 查询注册率
    query.append((db.func.sum(db.func.IF((Visitor.islogin), 1, 0)) / db.func.count(Visitor.id)).label("reg_rate"))
    # 查询占比
    query.append((db.func.count(Visitor.id) / sum).label("hold"))

    # 组装排序

    order = list()
    if vis_sort == "asc":
        order.append(db.asc("vis"))
    elif vis_sort =="desc" :
        order.append(db.desc("vis"))

    if reg_sort == "asc":
        order.append(db.asc("reg"))
    elif reg_sort == "desc":
        order.append(db.desc("reg"))

    if regRate_sort =="asc":
        order.append(db.asc("reg_rate"))
    elif regRate_sort == "desc":
        order.append(db.desc("reg_rate"))

    if hold_sort =="asc":
        order.append(db.asc("hold"))
    elif hold_sort == "desc":
        order.append(db.desc("hold"))
    print(order)
    ret = db.session.query(*query).filter(*sql).order_by(*order).group_by(Visitor.keyword).paginate(page, per_page=pageSize, error_out=False)
    data = [{
        "keyword": i.keyword,
        "vis": i.vis,
        "reg": int(i.reg),
        "regRate": '{:.2%}'.format(round(i.reg_rate,4)),
        "hold": '{:.2%}'.format(round(i.hold,4)),
        "createtime": str(i.createtime),
    } for i in ret.items]




    return jsonify(code=0, msg="成功",
                   page=ret.page,
                   total=ret.total,
                   pages=ret.pages,
                   rows=data, )



# 销售榜单
@app.route('/pdd/admin/sell/anal/rank', methods=["get"])
@admin_require
def admin_sell_anal_rank(aid):

    if not aid.job.Seo:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")

    data_type = request.args.get("data_type",default=1,type=int)

    if not data_type in [1,2,3]:
        return jsonify(code=-1, msg="访问出错")

    sql1 = list()
    sql2 = list()
    Now = datetime.datetime.now()
    if data_type ==2:
        sql1.append(db.cast(UserCoop.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=7))
        sql2.append(db.cast(SellDays.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=7))

    if data_type ==3:
        sql1.append(db.cast(UserCoop.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=30))
        sql2.append(db.cast(SellDays.createtime, db.DATE) >= Now.date() - datetime.timedelta(days=30))

    data = []

    ret = db.session.query(Admin.userPhoto, Admin.name, Admin.username,
                           db.func.Sum(UserCoop.price).label("amount")).join(UserCoop).join(Admin_job).filter(
        Admin_job.sell).filter(*sql1).order_by(
        db.desc("amount")).group_by(Admin.username).limit(3).all()

    data.append({
        "title": "销售金额榜",
        "data": [{
            "userPhoto": i.userPhoto,
            "name": i.name,
            "count": str(round(i.amount / 10000, 3)) + "W" if i.amount >= 100000 else i.amount
        } for i in ret]
    })

    ret = db.session.query(Admin.userPhoto, Admin.name, Admin.username, db.func.count(SellDays.id).label("count")).join(
        SellDays).join(Admin_job).filter(*sql2).filter(Admin_job.sell, SellDays.wechat_status == 2).order_by(
        db.desc("count")).group_by(Admin.username).limit(3).all()

    data.append({
        "title": "微信榜",
        "data": [{
            "userPhoto": i.userPhoto,
            "name": i.name,
            "count": i.count
        } for i in ret]
    })

    ret = db.session.query(Admin.userPhoto, Admin.name, Admin.username, db.func.count(UserCoop.id).label("count")).join(
        UserCoop).join(Admin_job).filter(Admin_job.sell).filter(*sql1).order_by(
        db.desc("count")).group_by(Admin.username).limit(3).all()

    data.append({
        "title": "合作单数",
        "data": [{
            "userPhoto": i.userPhoto,
            "name": i.name,
            "count": i.count
        } for i in ret]
    })

    return jsonify(code=0,msg="获取成功",data=data)

# 用户头像更换
@app.route('/pdd/admin/upload/photo', methods=["post"])
@admin_require
def admin_upload_photo(aid):
    photo = request.form.get("photo",type=str)

    if photo is None:
        return jsonify(code=-1,msg="头像更改失败～")

    if aid.userPhoto:
        QiniuDel(aid.userPhoto)

    aid.userPhoto = photo

    try:
        db.session.commit()
        return jsonify(code=0, msg="头像更改成功～")
    except:
        db.session.rollback()
        return jsonify(code=-1, msg="头像更改失败～")


# 销售数据信息
@app.route('/pdd/admin/anal/sell/data', methods=["get"])
@admin_require
def admin_anal_sell_data(aid):

    if not aid.job.allot:
        return jsonify(code=-103, msg="无权访问,需要管理员授权")
    page = request.args.get('page', default=1, type=int)
    pageSize = request.args.get('pageSize', default=10, type=int)
    call_count_sort = request.args.get('call_count_sort', type=str)
    wechat_count_sort = request.args.get('wechat_count_sort', type=str)
    intent_count_sort = request.args.get('intent_count_sort', type=str)
    wechat_rate_sort = request.args.get('wechat_rate_sort', type=str)
    coop_count_sort = request.args.get('coop_count_sort', type=str)

    order = list()
    if call_count_sort =="asc":
        order.append(db.asc("call_count"))
    elif call_count_sort == "desc":
        order.append(db.desc("call_count"))

    if wechat_count_sort == "asc":
        order.append(db.asc("wechat"))
    elif wechat_count_sort == "desc":
        order.append(db.desc("wechat"))

    if intent_count_sort == "asc":
        order.append(db.asc("intent"))
    elif intent_count_sort == "desc":
        order.append(db.desc("intent"))

    if wechat_rate_sort == "asc":
        order.append(db.asc("wechat_rate"))
    elif wechat_rate_sort == "desc":
        order.append(db.desc("wechat_rate"))

    if coop_count_sort == "asc":
        order.append(db.asc("coop"))
    elif coop_count_sort == "desc":
        order.append(db.desc("coop"))

    query = [db.func.count(User.id).label("call_count"), Admin.id, Admin.userPhoto, Admin.username, Admin.name]
    query.append(db.func.sum(db.func.IF((UserSell.wechat_status == 2), 1, 0)).label("wechat"))
    query.append(db.func.sum(db.func.IF((UserSell.Intent_status == 1), 1, 0)).label("intent"))
    query.append(db.func.sum(db.func.IF((UserSell.call_status == 0), 1, 0)).label("surplus"))
    query.append(
        (db.func.sum(db.func.IF((UserSell.wechat_status == 2), 1, 0)) / db.func.count(User.id)).label("wechat_rate"))
    query.append(db.func.sum(db.func.IF((UserCoop.endTime <= datetime.datetime.now().date()), 1, 0)).label("coop"))

    ret = db.session.query(*query).join(UserSell).join(Admin).outerjoin(UserCoop).outerjoin(Admin_job).filter(
        Admin_job.sell).group_by(Admin.id).order_by(*order).paginate(page, per_page=pageSize, error_out=False)
    data = [{
            "id": i.id,
            "username": i.username,
            "userPhoto": i.userPhoto,
            "name": i.name,
            "call_count": i.call_count,
            "wechat_count":int(i.wechat),
            "intent_count":int(i.intent),
            "surplus_count":int(i.surplus),
            "wechat_rate":'{:.2%}'.format(round(i.wechat_rate,4)),
            "coop_count":int(i.coop),
        }for i in ret.items]

    return jsonify(code=0, msg="成功",
                   page=ret.page,
                   total=ret.total,
                   pages=ret.pages,
                   rows=data, )

